﻿using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace Twi.DataAccess.Common
{
    public class TwiCommandOracle : TwiCommand
    {
        /// <summary>
        /// 参与会话所需的所有OracleCommand参数， OracleCommand每次操作时要求参数集合必须完全匹配, 加入这个集合用于对一个OracleCommand进行多次操作，每次操作OracleCommand只从该集合中抽取必要的参数。
        /// </summary>
        protected List<OracleParameter> MaximumOrclParameters = new List<OracleParameter>();

        public TwiCommandOracle(DbUtility db)
        {
            base.Initialize(db);
        }

        public TwiCommandOracle(DbUtility db, DbCommand cmd)
        {
            base.Initialize(db, cmd);
        }

        public TwiCommandOracle(DbUtility db, DbConnection conn, DbTransaction txn)
        {
            base.Initialize(db, conn, txn);
        }

        /// <summary>
        /// 获取 DbParameterCollection。
        /// 返回结果:Transact-SQL 语句或存储过程的参数。 默认值为空集合。
        /// </summary>
        public override DbParameterCollection Parameters
        {
            get
            {
                OracleCommand tempCmd = new OracleCommand();
                tempCmd.Parameters.AddRange(MaximumOrclParameters.ToArray());
                return tempCmd.Parameters;
            }
        }

        /// <summary>
        /// 初始化 DbParameter 类的新实例。
        /// </summary>
        /// <returns>返回 SqlParameter或OracleParameter 实例</returns>
        protected override DbParameter CreateDbParameter()
        {
            return new OracleParameter();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable()
        {
            DataTable dt = new DataTable();
            OracleDataAdapter sda = new OracleDataAdapter();
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            sda.SelectCommand = (_cmd as OracleCommand);
            sda.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <returns>受影响的行数。</returns>
        public override int ExecuteNonQuery()
        {
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);

            int ret = _cmd.ExecuteNonQuery();
            ExecuteCommit();
            return ret;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <returns>结果集中第一行的第一列。</returns>
        public override object ExecuteScalar()
        {
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            object ret = _cmd.ExecuteScalar();
            return ret;
        }

        /// <summary>
        /// 执行查询，返回DbDataReader
        /// </summary>
        /// <returns></returns>
        public override DbDataReader ExecuteReader()
        {
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            return _cmd.ExecuteReader();
        }

        public override DataTable ExecuteDatatable()
        {
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            return base.ExecuteDatatable();
        }

        #region 新增记录，返回新增记录的ID
        /// <summary>
        /// 新增记录，返回新增记录的ID
        /// </summary>
        /// <param name="xe"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override object ExecuteInsert(XElement xe, string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }

            //表的自增长列(Oracle用到)
            XElement xeSequence = _DB.GetSequenceName(tableName);

            //上传的节点名称跟数据库列名称相符合的节点集合  
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = _DB.GetMetaDataTable(tableName).Rows.OfType<DataRow>();
            foreach (XElement x in xe.Elements().Where(y => !new string[] { "CreateUserID", "CreateUserName", "CreateDate", "LastUpdateUserID", "LastUpdateUserName", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                //匹配列名
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName.ToUpper()).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数(排除自增长列参数)
                    if (xeSequence == null || (xeSequence != null && xeSequence.Name.LocalName != x.Name.LocalName))
                    {
                        //日期类型处理（因为客户端传过来的是1900-01-24T00:00:00或其他）
                        string dataType = dr["类型"].ToString().ToUpper();
                        if (dataType == "DATE" || dataType.StartsWith("TIMESTAMP"))
                        {
                            DateTime time;
                            if (DateTime.TryParse(x.Value, out time))
                            {
                                SetParameter(x.Name.LocalName, time);
                            }
                            else
                            {
                                SetParameter(x.Name.LocalName, DBNull.Value);
                            }
                        }
                        else if (dataType == "XMLTYPE")
                        {
                            SetParameter(x.Name.LocalName, x.HasElements ? (object)x : (object)DBNull.Value);
                        }
                        else if (dataType == "RAW")
                        {
                            SetParameter(x.Name.LocalName, x.Value != "" ? new Guid(x.Value).ToByteArray() : Guid.Empty.ToByteArray());
                        }
                        else if (dataType.Contains("CHAR") && x.HasElements) //数据库是字符窜类型，但客户端显示成XElement，因为该字段需要进行LIKE过滤
                        {
                            SetParameter(x.Name.LocalName, x.ToString());
                        }
                        else
                            SetParameter(x.Name.LocalName, x.Value);
                        setColumnList.Add(x);
                    }
                }
            }

            string insertIntoClause_Columns = setColumnList.Select(x => x.Name.LocalName).Aggregate(
                (x, y) => string.Format("{0}, {1}", x, y));
            string selectClause = setColumnList.Select(x => string.Format("{0}{1}", ":", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));

            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserID".ToUpper()) != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserID";
                selectClause += ", :CurrentUserID";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserName".ToUpper()) != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserName";
                selectClause += ", :CurrentUserName";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "LastUpdateDate".ToUpper()) != null)
            {
                insertIntoClause_Columns += ", LastUpdateDate";
                selectClause += ", :CurrentDate ";
                SetParameter("CurrentDate", GetCurrentDate());
            }

            if (xeSequence == null)//无序列
            {
                _cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, insertIntoClause_Columns, selectClause);
                return ExecuteNonQuery();
            }
            else
            {
                _cmd.CommandText = string.Format("SELECT {0}.NEXTVAL FROM DUAL", xeSequence.Value);
                object seqValue = _cmd.ExecuteScalar();
                //如果有序列假设肯定是只有一个主键的
                DataRow dr = metaRows.Where(y => y["主键"].ToString() == "1").FirstOrDefault();

                insertIntoClause_Columns += ", " + dr["字段名"].ToString();
                selectClause += ", " + ":" + dr["字段名"].ToString();
                SetParameter(dr["字段名"].ToString(), seqValue);
                _cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, insertIntoClause_Columns, selectClause);

                ExecuteNonQuery();
                return seqValue;
            }

        }
        #endregion
        #region 更新记录
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="xe"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override int ExecuteUpdate(XElement xe, string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }

            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = _DB.GetMetaDataTable(tableName).Rows.OfType<DataRow>();
            var primaryKeys = _DB.GetMetaDataTablePrimaryKeys(tableName);//主键集合 

            foreach (XElement x in xe.Elements().Where(y => !new string[] { "CreateUserID", "CreateUserName", "CreateDate", "LastUpdateUserID", "LastUpdateUserName", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                string value = x.Element("NewValue") == null ? (x.Value) : x.Element("NewValue").Value;
                //匹配列名
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName.ToUpper()).FirstOrDefault();
                if (dr != null)
                {
                    string dataType = dr["类型"].ToString().ToUpper();
                    //添加参数
                    //日期类型处理（因为客户端传过来的是1900-01-24T00:00:00或其他）
                    if (dataType == "DATE" || dataType.StartsWith("TIMESTAMP"))
                    {
                        DateTime time;
                        if (DateTime.TryParse(x.Value, out time))
                        {
                            SetParameter(x.Name.LocalName, time);
                        }
                        else
                        {
                            SetParameter(x.Name.LocalName, DBNull.Value);
                        }
                    }
                    else if (dataType == "RAW")
                    {
                        SetParameter(x.Name.LocalName, value != "" ? new Guid(value).ToByteArray() : Guid.Empty.ToByteArray());
                    }
                    else if (dataType.Contains("CHAR") && x.HasElements && x.Element("NewValue") == null && x.Element("OldValue") == null) //数据库是字符窜类型，但客户端显示成XElement，因为该字段需要进行LIKE过滤
                    {
                        SetParameter(x.Name.LocalName, x.ToString());
                    }
                    else if (dataType == "XMLTYPE")
                    {
                        SetParameter(x.Name.LocalName, x.HasElements ? (object)x : (object)DBNull.Value);
                    }
                    else
                        SetParameter(x.Name.LocalName, value);
                    if (!primaryKeys.Contains(x.Name.LocalName.ToUpper()) //排除主键(包括组合键)
                        && dr["主键"].ToString() == "0")  //标识主键列
                    {
                        setColumnList.Add(x);
                    }
                }
            }

            string setClause = setColumnList.Select(x => string.Format("{0} = :{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserID".ToUpper())) != null) //表中是否包含最后更新人字段
            {
                setClause += ", LastUpdateUserID = :CurrentUserID, LastUpdateDate = SysDate";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserName".ToUpper())) != null) //不是所有表都有
            {
                setClause += ", LastUpdateUserName = :CurrentUserName";
            }
            string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, setClause
                                    , primaryKeys.Select(x => string.Format("{0} = :{0}", x)).Aggregate((x, y) => string.Format("{0} AND {1}", x, y)));

            _cmd.CommandText = sql;

            return ExecuteNonQuery();

        }
        #endregion

        #region 获得单条数据
        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>查询到记录返回，没有找到记录抛出异常</returns>
        public override XElement GetSingle(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            using (var rdr = _cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetDataTypeName(i) == "XmlType")
                        {
                            xe.Add(rdr.ToXElement(_cmd, i, true));
                        }
                        else
                        {
                            xe.Add(rdr.ToXElement(_cmd, i));
                        }
                    }
                }
                else
                {
                    throw new Exception("无法检索到唯一记录");
                }
            }
            return xe;
        }
        #endregion

        #region 获得单条数据
        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override XElement GetSingleOrEmpty(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (var rdr = ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetDataTypeName(i) == "XmlType")
                        {
                            xe.Add(rdr.ToXElement(_cmd, i, true));
                        }
                        else
                        {
                            xe.Add(rdr.ToXElement(_cmd, i));
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xe.Add(new XElement(rdr.GetName(i)));
                    }
                }
            }
            return xe;
        }
        #endregion

        #region 获得单条数据
        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>查询到记录返回，没有找到记录返回null</returns>
        public override XElement GetSingleOrDefault(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (var rdr = ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetDataTypeName(i) == "XmlType")
                        {
                            xe.Add(rdr.ToXElement(_cmd, i, true));
                        }
                        else
                        {
                            xe.Add(rdr.ToXElement(_cmd, i));
                        }
                    }
                }
                else
                {
                    return null;
                }
            }
            return xe;
        }
        #endregion

        #region 获得查询列表
        /// <summary>
        /// 获得查询列表
        /// </summary>
        /// <param name="tableName">节点名称</param>
        /// <returns></returns>
        public override XElement GetMultiple(string tableName = "Item")
        {
            XElement xeMultiple = new XElement(string.Format("{0}List", tableName));

            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            using (var rdr = _cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xe = new XElement(tableName);
                    xeMultiple.Add(xe);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetDataTypeName(i) == "XmlType")
                        {
                            xe.Add(rdr.ToXElement(_cmd, i, true));
                        }
                        else
                        {
                            xe.Add(rdr.ToXElement(_cmd, i));
                        }
                    }
                }
            }
            return xeMultiple;
        }
        /// <summary>
        /// 获取数据(分页)
        /// </summary>
        /// <param name="masterTableName"></param>
        /// <param name="sql"></param>
        /// <param name="searchCondition"></param>
        /// <param name="otherText"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public override XElement[] GetMultiple(string masterTableName, string sql, string searchCondition, string otherText, int pageSize, int pageIndex)
        {
            string source = string.Format(@"WITH VIEW_SOURCE AS ({0} {1} {2} )", sql, searchCondition, otherText);

            //处理分页信息
            _cmd.CommandText = source + Environment.NewLine + string.Format("SELECT COUNT(*) FROM VIEW_SOURCE");
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            object objItemAmount = null;
            try
            {
                objItemAmount = _cmd.ExecuteScalar();
            }
            catch
            {
                throw new Exception("查找总条目数异常: " + Environment.NewLine + _cmd.CommandText);
            }
            int itemAmount = int.Parse(objItemAmount.ToString());
            int pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
            XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
            XElement xePageAmount = new XElement("PageAmount", pageAmount);

            XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName ?? "Item"));

            if (itemAmount == 0)
            {
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }

            string source_numbered = source +
                Environment.NewLine + string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT VIEW_SOURCE.* FROM VIEW_SOURCE)") +
                Environment.NewLine + string.Format(", VIEW_SOURCE_ROWNUMBERED2 AS (SELECT VIEW_SOURCE_ROWNUMBERED.*,Rownum rn FROM VIEW_SOURCE_ROWNUMBERED WHERE Rownum <={0})", pageSize * (pageIndex + 1));

            //处理列表信息
            _cmd.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT * FROM VIEW_SOURCE_ROWNUMBERED2 WHERE rn > {0} ", pageSize * pageIndex);


            using (var rdr = ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(masterTableName ?? "Item");
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xeItem.Add(rdr.ToXElement(_cmd, i));
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
        }
        #endregion

        #region 获得数据(分页/或不分页)
        /// <summary>
        /// 获得数据(分页/或不分页)
        /// </summary>
        /// <param name="masterTableName"></param>
        /// <param name="tablesSource"></param>
        /// <param name="searchCondition"></param>
        /// <param name="selectColumns"></param>
        /// <param name="orderExpression"></param>
        /// <param name="xePage"></param>
        /// <param name="subSearchCondition"></param>
        /// <param name="subOrderExpression"></param>
        /// <returns></returns>
        public override XElement[] GetMultiple(string masterTableName, string tablesSource, string searchCondition, string selectColumns, string orderExpression
            , XElement xePage, string subSearchCondition = "", string subOrderExpression = "")
        {
            if (xePage.Element("PageIndex") == null) //不需要分页
            {
                _cmd.CommandText = "SELECT " + selectColumns + " FROM " + tablesSource + " " + searchCondition + " ORDER BY " + orderExpression;
                return new XElement[] { GetMultiple(masterTableName) };
            }
            else if (xePage.Element("PageIndex").Value == "-1") //分页转不分页，导出全部记录时使用
            {
                _cmd.CommandText = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1 = 1 {2} {3})"
                , subOrderExpression == "" ? selectColumns : (" " + selectColumns), tablesSource, subSearchCondition
                , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));
                //处理自定义的排序子句
                string orderby = "";
                if (!string.IsNullOrEmpty(orderExpression))
                {
                    //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                    orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                string subOrderby = "";
                if (!string.IsNullOrEmpty(subOrderExpression))
                {
                    subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                _cmd.CommandText = _cmd.CommandText + Environment.NewLine + "SELECT VIEW_SOURCE.* FROM VIEW_SOURCE " + searchCondition;
                string sqlOrderby = string.IsNullOrEmpty(orderby) ? (string.IsNullOrEmpty(subOrderExpression) ? "" : subOrderby) : orderby;
                if (!string.IsNullOrEmpty(sqlOrderby))
                {
                    _cmd.CommandText += " ORDER BY " + sqlOrderby;
                }
                return new XElement[] { GetMultiple(masterTableName) };
            }
            else
            {
                return GetMultiple(masterTableName, tablesSource, searchCondition, selectColumns, orderExpression, int.Parse(xePage.Element("PageSize").Value), int.Parse(xePage.Element("PageIndex").Value), subSearchCondition, subOrderExpression);
            }
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="masterTableName">主表名称</param>
        /// <param name="tablesSource">数据的来源from后（主表 inner join 从表 on 。。）</param>
        /// <param name="searchCondition">搜索条件 where 后（..）/param>
        /// <param name="selectColumns">select后</param>
        /// <param name="orderExpression"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="subSearchCondition">跟在SQl内部的Where条件</param>
        /// <returns></returns>
        private XElement[] GetMultiple(string masterTableName, string tablesSource, string searchCondition, string selectColumns, string orderExpression
            , int pageSize, int pageIndex, string subSearchCondition, string subOrderExpression)
        {
            string source = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1=1 {2} {3})"
                , subOrderExpression == "" ? selectColumns : (" " + selectColumns), tablesSource, subSearchCondition
                , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));

            //处理分页信息
            _cmd.CommandText = source + Environment.NewLine + string.Format("SELECT COUNT(*) FROM VIEW_SOURCE {0} ", searchCondition);
            (_cmd as OracleCommand).PrepareOracleParameters(MaximumOrclParameters);
            object objItemAmount = null;
            try
            {
                objItemAmount = _cmd.ExecuteScalar();
            }
            catch
            {
                throw new Exception("查找总条目数异常: " + Environment.NewLine + _cmd.CommandText);
            }
            int itemAmount = int.Parse(objItemAmount.ToString());
            int pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
            XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
            XElement xePageAmount = new XElement("PageAmount", pageAmount);

            XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName ?? "Item"));

            if (itemAmount == 0)
            {
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }

            //处理自定义的排序子句
            string orderby = "";
            if (!string.IsNullOrEmpty(orderExpression))
            {
                //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            string subOrderby = "";
            if (!string.IsNullOrEmpty(subOrderExpression))
            {
                subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            //2013-08-27 优化分页查询注视掉
            //string source_numbered = source + Environment.NewLine +
            //    string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber, VIEW_SOURCE.* FROM VIEW_SOURCE {1})"
            //    , string.IsNullOrEmpty(orderby) ? (string.IsNullOrEmpty(subOrderExpression) ? "(SELECT NULL FROM DUAL)" : subOrderby) : orderby
            //    , searchCondition);

            //处理列表信息
            //_cmd.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT /*+ FIRST_ROWS */ * FROM VIEW_SOURCE_ROWNUMBERED WHERE RowNumber > {0} AND RowNumber<={1} ORDER BY RowNumber"
            //    , pageSize * pageIndex, pageSize * (pageIndex + 1));


            string source_numbered = source + Environment.NewLine +
                string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT VIEW_SOURCE.*,Rownum rn FROM VIEW_SOURCE {0} ORDER BY {1})"
                , searchCondition
                , string.IsNullOrEmpty(orderby) ? (string.IsNullOrEmpty(subOrderExpression) ? "(SELECT NULL FROM DUAL)" : subOrderby) : orderby);

            //处理列表信息
            _cmd.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT * FROM VIEW_SOURCE_ROWNUMBERED WHERE rn between {0} AND {1} "
                , pageSize * pageIndex
                , pageSize * (pageIndex + 1));

            using (var rdr = ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(masterTableName ?? "Item");
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xeItem.Add(rdr.ToXElement(_cmd, i));
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
        }
        #endregion

        #region 获得数据库当前时间
        /// <summary>
        /// 获得数据库当前时间
        /// </summary>
        /// <returns></returns>
        public override DateTime GetCurrentDate()
        {
            _cmd.CommandText = "SELECT sysdate FROM DUAL";
            return (DateTime)_cmd.ExecuteScalar();
        }
        #endregion

        #region 提交
        /// <summary>
        /// 提交
        /// </summary>
        /// <param name="me"></param>
        public void ExecuteCommit()
        {
            //提交
            if (_cmd.Transaction == null)
            {
                _cmd.Parameters.Clear();
                string originalCommandText = _cmd.CommandText;
                _cmd.CommandText = "COMMIT";
                _cmd.ExecuteNonQuery();
                _cmd.CommandText = originalCommandText;
            }
        }
        #endregion

        #region 添加参数
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        public override void AddParameter(string paraName, object paraValue)
        {
            paraName = paraName.ToUpper();
            if (MaximumOrclParameters.Any(x => x.ParameterName.ToUpper() == paraName.ToUpper()))
            {
                MaximumOrclParameters.Single(x => x.ParameterName.ToUpper() == paraName.ToUpper()).Value = paraValue;
            }
            else
            {
                MaximumOrclParameters.Add(ToParameter(paraName, paraValue) as OracleParameter);
            }
        }
        #endregion
        #region XML To Parameter
        public DbParameter ToParameter<T>(XElement me)
        {
            if (typeof(T) == typeof(XElement))
            {
                return ToParameter(me.Name.LocalName, me);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                return ToParameter(me.Name.LocalName, DateTime.Parse(me.Value));
            }
            else if (typeof(T) == typeof(Guid))
            {
                return ToParameter(me.Name.LocalName, new Guid(me.Value));
            }
            else
            {
                return ToParameter(me.Name.LocalName, me.Value);
            }
        }
        public DbParameter ToParameter(string paraName, object paraValue)
        {
            OracleParameter para = null;
            if (paraValue is XElement)
            {
                para = new Oracle.DataAccess.Client.OracleParameter(paraName, Oracle.DataAccess.Client.OracleDbType.XmlType);
            }
            else if (paraValue is DateTime)
            {
                para = new Oracle.DataAccess.Client.OracleParameter(paraName, Oracle.DataAccess.Client.OracleDbType.Date);
            }
            else if (paraValue is Guid)
            {
                para = new Oracle.DataAccess.Client.OracleParameter(paraName, Oracle.DataAccess.Client.OracleDbType.Raw);
                paraValue = ((Guid)paraValue).ToByteArray();
            }
            else
            {
                para = new Oracle.DataAccess.Client.OracleParameter();
                para.ParameterName = paraName;
            }
            para.Value = paraValue;
            return para;
        }
        #endregion
        #region 设置参数值(参数存在设置值，不存在添加参数)
        /// <summary>
        /// 设置参数值(参数存在设置值，不存在添加参数)
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        public override void SetParameter(string paraName, object paraValue)
        {
            paraName = paraName.ToUpper();
            if (paraValue is XElement)
            {
                paraValue = new OracleXmlType(_cmd.Connection as OracleConnection, new XmlTextReader(paraValue.ToString(), XmlNodeType.Document, null));
            }
            OracleParameter para = MaximumOrclParameters.FirstOrDefault(x => x.ParameterName == paraName);
            if (para == null)
            {
                AddParameter(paraName, paraValue);
            }
            else
            {
                para.Value = paraValue;
            }
        }

        /// <summary>
        /// 设置参数值(参数存在设置值，不存在添加参数)
        /// </summary>
        /// <param name="parameter">参数</param>
        public override void SetParameter(DbParameter parameter)
        {
            OracleParameter para = MaximumOrclParameters.FirstOrDefault(x => x.ParameterName.ToUpper() == parameter.ParameterName.ToUpper());
            if (para == null)
            {
                MaximumOrclParameters.Add(parameter as OracleParameter);
            }
            else
            {
                //para.OracleDbType = (parameter as OracleParameter).OracleDbType;
                para.Value = parameter.Value;
            }
        }


        #endregion
        #region 设置参数并返回当前参数
        /// <summary>
        /// 设置参数并返回当前参数
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="type"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public override DbParameter SetParameter_InputOutput(string paraName, TwiDbType type, int size = 0)
        {
            //if (!paraName.StartsWith("@"))
            //{
            //    paraName = "@" + paraName;
            //}
            if (_cmd.Parameters.Contains(paraName))
            {
                return _cmd.Parameters[paraName];
            }
            else
            {
                OracleParameter para = new OracleParameter();
                para.ParameterName = paraName;
                para.Direction = ParameterDirection.InputOutput;
                para.Value = DBNull.Value;
                para.OracleDbType = (OracleDbType)type;
                if (size != 0)
                {
                    para.Size = size;
                }
                MaximumOrclParameters.Add(para);
                return para;
            }
        }
        #endregion

        #region 连接字符串
        /// <summary>
        /// 连接字符串
        /// </summary>
        /// <param name="separartor"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        public override string GetConcatenate(string separartor, string format = "{0}")
        {
            string concat = "";
            using (var rdr = ExecuteReader())
            {
                while (rdr.Read())
                {
                    if (concat != "")
                    {
                        concat += separartor;
                    }
                    concat += string.Format(format, rdr.GetValue(0));
                }
            }
            return concat;
        }
        #endregion

    }
}
